package com.elitel.frame.business.service.impl;

import com.elitel.base.entity.sqlparse.InputParam;
import com.elitel.base.entity.sqlparse.SqlData;
import com.elitel.base.entity.sqlparse.SqlParser;
import com.elitel.common.core.exception.GlobalException;
import com.elitel.common.core.log.enums.ResultCodeEnum;
import com.elitel.common.utils.SpringUtils;
import com.elitel.common.utils.StringUtils;
import com.elitel.frame.base.service.CfgDbService;
import com.elitel.frame.main.entity.CfgDb;
import freemarker.cache.StringTemplateLoader;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import org.apache.ibatis.builder.ParameterExpression;
import org.apache.ibatis.builder.StaticSqlSource;
import org.apache.ibatis.builder.xml.XMLMapperBuilder;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.parsing.GenericTokenParser;
import org.apache.ibatis.parsing.TokenHandler;
import org.apache.ibatis.parsing.XNode;
import org.apache.ibatis.scripting.defaults.RawSqlSource;
import org.apache.ibatis.scripting.xmltags.*;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.StringWriter;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static com.elitel.base.entity.sqlparse.ParamExpType.jdbcType;
import static com.elitel.base.entity.sqlparse.ParamExpType.property;
import static com.elitel.base.entity.sqlparse.ParamType.*;
import static java.util.stream.Collectors.toList;


public class SqlUtlil2 {

    public static Map<String, Object> parseSql(String dbKey, String sql) {
        DbConf dbConf = new DbConf();
        CfgDbService bean = SpringUtils.getBean(CfgDbService.class);
        CfgDb cfgDb = bean.selectByKey(dbKey);
        dbConf.dbUrl = cfgDb.getDbUrl();
        dbConf.dbDrivername = cfgDb.getDbDrivername();
        dbConf.dbUser = cfgDb.getDbUser();
        dbConf.dbPwd = cfgDb.getDbPwd();
        dbConf.sql = sql;
        SqlUtlil2 sqlUtlil = new SqlUtlil2();
        if (sql.contains("#if")) {
            return sqlUtlil.parseFreeMarkerSql(dbConf);
        } else {
            return sqlUtlil.parseSql(dbConf);
        }
    }

    public Map<String, Object> parseSql(DbConf dbConf) {
        Map<String, Object> selectItems = getSelectItems(dbConf);
        List<InputParam> whereParam = getWhereParam(dbConf.getSql());
        String tablesStr = getTablesStr(dbConf.getSql());
        Map<String, Object> map = new HashMap<>();
        map.put("selectItems", selectItems);
        map.put("whereParam", whereParam);
        map.put("tables", tablesStr);
        return map;
    }

    /*处理FreeMarkerSql*/
    public Map<String, Object> parseFreeMarkerSql(DbConf dbConf) {
        String sql = dbConf.getSql();
        String tmp = free2mybatis(sql);
        String newSql = dealCdata(tmp);
        dbConf.setSql(newSql);
        Map<String, Object> selectItems = getSelectItems(dbConf);
        List<InputParam> whereParam = getWhereParam(dbConf.sql);
        String tablesStr = getTablesStr(dbConf.sql);
        Map<String, Object> map = new HashMap<>();
        map.put("selectItems", selectItems);
        map.put("whereParam", whereParam);
        map.put("tables", tablesStr);
        return map;
    }

    private static String dealCdata(String input) {
        Pattern pattern = Pattern.compile(
                "<if\\s+test\\s*=\\s*\"[^\"]*\"\\s*>",
                Pattern.DOTALL // 允许跨行匹配
        );
        Matcher matcher = pattern.matcher(input);
        String sql = input;
        HashMap<String, String> map = new HashMap<>();
        while (matcher.find()) {
            String condition = matcher.group(); // 获取条件表达式

            map.put(condition, UUID.randomUUID().toString());
        }
        map.put("</if>", UUID.randomUUID().toString());
        for (Map.Entry<String, String> entry : map.entrySet()) {
            sql = sql.replace(entry.getKey(), entry.getValue());
        }

        sql = sql.replace("<", " <![CDATA[ < ]]> ");

        for (Map.Entry<String, String> entry : map.entrySet()) {
            sql = sql.replace(entry.getValue(), entry.getKey());
        }
        return sql;
    }

    public static String free2mybatis(String templateContent) {
        Map<String, String> map = new HashMap<>();
//        String regex = "<#if\\s+(\\w+)\\s+\\?\\?\\s+\\&\\&\\s+\\1\\s+!=\\s*\'.*?\'>";
//        Pattern pattern = Pattern.compile(regex);
        Pattern pattern = Pattern.compile("<#if\\s+([^>]+)>");
        Matcher matcher = pattern.matcher(templateContent);
        while (matcher.find()) {
            String pp = matcher.group();
            String replace = pp.replace("??", " != null ")
                    .replace("<#if", "")
                    .replace(">", "");
            if (pp.contains("&&")) replace = replace.replace("&&", " and ");
            map.put(pp, "<if test=\"" + replace + "\">");
        }
        String sql = templateContent;
        for (Map.Entry<String, String> entry : map.entrySet()) {
            sql = sql.replace(entry.getKey(), entry.getValue());
        }
        String tr = sql.replace("</#if>", "</if>")
                .replace("'${", "#{")
                .replace("}'", "}");
        System.out.println(tr);
        return tr;
    }

    public static String getTablesStr(String sql) {
        Set<String> fromTables = getFromTables(sql);
        Set<String> withAsNames = getWithAsNames(sql);
        fromTables.removeAll(withAsNames);
        return fromTables.toString().replace("[", "").replace("]", "");
    }

    public static Set<String> getFromTables(String sql) {
        Set<String> tables = new HashSet<>();
        sql = sql.replaceAll("/\\*.*?\\*/", "");
        sql = sql.replaceAll("--.*", "");
        sql.replaceAll("$[^()]*$", "");
        Pattern pattern = Pattern.compile(
                "(?i)\\b(?:FROM|JOIN)\\s+((?:[\\w\\.]+)(?:\\s+AS\\s+\\w+)?)",
                Pattern.CASE_INSENSITIVE
        );
        Matcher matcher = pattern.matcher(sql);
        while (matcher.find()) {
            String rawTable = matcher.group(1);
            String tableName = rawTable.split("\\s+AS\\s+")[0].trim();
            tables.add(tableName);
        }
        return tables;
    }

    public static Set<String> getWithAsNames(String sql) {
        Pattern pattern = Pattern.compile("WITH\\s+(?:`?)([\\w.]+)(?:`?)\\s+AS", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);
        Set<String> set = new HashSet<>();
        while (matcher.find()) {
            String tableName = matcher.group(1);
            set.add(tableName);
        }
        return set;
    }

    public Map<String, Object> getSelectItems(DbConf dbConf) {
        // urls = "jdbc:oracle:thin:@192.168.0.178:1521:orcl";
        Map<String, Object> map = new HashMap<>();
        Connection con = null; // 创建一个数据库连接
        PreparedStatement pre = null; // 创建预编译语句对象，一般都是用这个而不用Statement
        ResultSet result = null; // 创建一个结果集对象
        ResultSetMetaData resultSetMetaData = null;
        try {
            Class.forName(dbConf.getDbDrivername()); // 加载驱动程序
            System.out.println("开始尝试连接数据库！");
            String url = dbConf.getDbUrl(); // 127.0.0.1是本机地址，XE是精简版Oracle的默认数据库名
            String user = dbConf.getDbUser(); // 用户名,系统默认的账户名
            String password = dbConf.getDbPwd(); // 你安装时选设置的密码
            con = DriverManager.getConnection(url, user, password); // 获取连接
            System.out.println("连接成功！");
            Map<String, Object> map2 = new HashMap<>();
            String sql = "";
            SqlData sqlData = parseSQLGetField(dbConf.getSql(), new HashMap<>());
            sql = sqlData.getSql();
            //避免全表扫描
            sql = "select * from (" + sql + ") a where 1=0";
            pre = con.prepareStatement(sql); // 实例化预编译语句

            // 统计 SQL 语句中占位符的数量
            int placeholderCount = sql.replaceAll("[^?]", "").length();

            if (!sqlData.getParamList().isEmpty()) {
                int index = 0;
                for (int i = 0; i < placeholderCount; i++) {
                    pre.setNull(++index, Types.NULL);
                }
            }


            result = pre.executeQuery(); // 执行查询，注意括号中不需要再加参数
            resultSetMetaData = result.getMetaData();
            //String aString = String.valueOf(resultSetMetaData.getColumnCount());
            for (int i = 1; i <= Integer.parseInt(String.valueOf(resultSetMetaData.getColumnCount())); i++) {
                String columnType = "";
        /*System.out.println(resultSetMetaData.getColumnName(i) + "++++++++++++++++"
        + resultSetMetaData.getColumnTypeName(i));*/
                String as = resultSetMetaData.getColumnTypeName(i);
                if ("TIME".equals(as.toUpperCase())) {
                    columnType = "date";
                } else if ("DATETIME".equals(as.toUpperCase())) {
                    columnType = "date";
                } else if ("timestamp".equals(as.toLowerCase())) {
                    columnType = "date";
                } else if ("date".equals(as.toLowerCase())) {
                    columnType = "date";
                } else if ("char".equals(as.toLowerCase())) {
                    columnType = "string";
                } else if ("text".equals(as.toLowerCase())) {
                    columnType = "string";
                } else if ("varchar2".equals(as.toLowerCase())) {
                    columnType = "string";
                } else if ("nvarchar2".equals(as.toLowerCase())) {
                    columnType = "string";
                } else if ("nvarchar".equals(as.toLowerCase())) {
                    columnType = "string";
                } else if ("varchar".equals(as.toLowerCase())) {
                    columnType = "string";
                } // Add By Cocoliu 2017/11/9 增加clob、nclob、blob类型
                else if ("clob".equals(as.toLowerCase())) {
                    columnType = "clob";
                } else if ("nclob".equals(as.toLowerCase())) {
                    columnType = "clob";
                } else if ("blob".equals(as.toLowerCase())) {
                    columnType = "blob";
                } else {
                    columnType = "int";
                }
                // System.out.println(resultSetMetaData.getColumnTypeName(i)+"==========================="+resultSetMetaData.getColumnName(i));
                map.put(resultSetMetaData.getColumnName(i), columnType);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new GlobalException(ResultCodeEnum.SQL_EXCEPTION, String.valueOf(e));
        } finally {
            try {
                // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
                // 注意关闭的顺序，最后使用的最先关闭
                if (result != null) result.close();
                if (pre != null) pre.close();
                if (con != null) con.close();
                System.out.println("数据库连接已关闭！");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return map;
    }

    public static List<InputParam> getWhereParam(String xml) {
        SqlSource sqlSource = xmlVerify(xml);
        Map<String, InputParam> map1 = new HashMap<>();
        List<InputParam> whereParam = parseWhereParam(sqlSource);
        for (InputParam inputParam : whereParam) {
            map1.put(inputParam.getName(), inputParam);
        }
        List<InputParam> whereParam2=new ArrayList<>();
        whereParam2.addAll(map1.values());
        return whereParam2;
    }

    public static List<InputParam> parseWhereParam(SqlSource sqlSource) {
        if (sqlSource instanceof RawSqlSource) {
            return parseRawParam((RawSqlSource) sqlSource);
        }
        if (sqlSource instanceof DynamicSqlSource) {
            return parseDynamicParam((DynamicSqlSource) sqlSource);
        }
        return Collections.emptyList();
    }

    private static List<InputParam> parseDynamicParam(DynamicSqlSource sqlSource) {
        SqlNode sqlNode = getFieldValue(sqlSource, "rootSqlNode");
        return parseSqlNode(sqlNode, true);
    }

    private static List<InputParam> parseSqlNode(SqlNode sqlNode, boolean required) {
        if (sqlNode instanceof MixedSqlNode) {
            return parseMixedSqlNodeParam((MixedSqlNode) sqlNode, required);
        }
        if (sqlNode instanceof TextSqlNode) {
            return parseTextSqlNodeParam((TextSqlNode) sqlNode, required);
        }
        if (sqlNode instanceof StaticTextSqlNode) {
            return parseStaticTextSqlNodeParam((StaticTextSqlNode) sqlNode, required);
        }
        if (sqlNode instanceof IfSqlNode) {
            return parseIfSqlNodeParam((IfSqlNode) sqlNode);
        }
        if (sqlNode instanceof ForEachSqlNode) {
            return parseForEachSqlNodeParam((ForEachSqlNode) sqlNode, required);
        }
        if (sqlNode instanceof ChooseSqlNode) {
            return parseChooseSqlNodeParam((ChooseSqlNode) sqlNode);
        }
        if (sqlNode instanceof TrimSqlNode) {
            return parseTrimSqlNodeParam((TrimSqlNode) sqlNode, required);
        }
        if (sqlNode instanceof VarDeclSqlNode) {
            return parseVarDeclSqlNodeParam((VarDeclSqlNode) sqlNode, required);
        }
        return Collections.emptyList();
    }

    private static List<InputParam> parseVarDeclSqlNodeParam(VarDeclSqlNode sqlNode, boolean required) {
        String name = getFieldValue(sqlNode, "name");
        String expression = getFieldValue(sqlNode, "expression");
        return Collections.singletonList(new InputParam(name, BIND, expression, required));
    }

    private static List<InputParam> parseTrimSqlNodeParam(TrimSqlNode sqlNode, boolean required) {
        SqlNode contents = getFieldValue(sqlNode, "contents");
        return parseSqlNode(contents, required);
    }

    private static List<InputParam> parseChooseSqlNodeParam(ChooseSqlNode sqlNode) {
        List<InputParam> chooseParamList = new ArrayList<InputParam>();

        List<SqlNode> ifSqlNodes = getFieldValue(sqlNode, "ifSqlNodes");
        ifSqlNodes.forEach(content -> chooseParamList.addAll(parseSqlNode(content, false)));

        SqlNode defaultSqlNode = getFieldValue(sqlNode, "defaultSqlNode");
        if (defaultSqlNode != null) {
            chooseParamList.addAll(parseSqlNode(defaultSqlNode, false));
        }
        return chooseParamList;
    }

    private static List<InputParam> parseForEachSqlNodeParam(ForEachSqlNode sqlNode, boolean required) {
        List<InputParam> forEachParamList = new ArrayList<InputParam>();
        String collectionExpression = getFieldValue(sqlNode, "collectionExpression");
        //todo 没有.的情况要兼容
        if (collectionExpression.contains("."))
            collectionExpression = collectionExpression.substring(0, collectionExpression.indexOf("."));
        String item = getFieldValue(sqlNode, "item");
        if (item != null) {
            SqlNode forEachSqlNode = getFieldValue(sqlNode, "contents");
            List<InputParam> paramList = parseSqlNode(forEachSqlNode, false);
            String finalCollectionExpression = collectionExpression;
            paramList.stream()
                    .filter(param -> param.getName() != null && param.getName().equals(item))
                    .findFirst()
                    .ifPresent(inputParam -> forEachParamList.add(
                            new InputParam(finalCollectionExpression, PRE_COMPILE, null, required)
                    ));

        } else {
            forEachParamList.add(new InputParam(collectionExpression, PRE_COMPILE, null, required));
        }


        return forEachParamList;
    }

    private static List<InputParam> parseIfSqlNodeParam(IfSqlNode sqlNode) {
        SqlNode contents = getFieldValue(sqlNode, "contents");
        return parseSqlNode(contents, false);
    }

    private static List<InputParam> parseStaticTextSqlNodeParam(StaticTextSqlNode sqlNode, boolean required) {
        TextSqlNodeTokenHandler handler = new TextSqlNodeTokenHandler();
        GenericTokenParser parser = new GenericTokenParser("#{", "}", handler);
        parser.parse(getFieldValue(sqlNode, "text"));

        return handler.getParamSet().stream()
                .map(param -> {
                    ParameterExpression parameterExpression = new ParameterExpression(param);
                    return new InputParam(parameterExpression.get(property.name()), PRE_COMPILE, null, required,
                            parameterExpression.get(jdbcType.name()));
                })
                .collect(toList());
    }

    private static List<InputParam> parseMixedSqlNodeParam(MixedSqlNode sqlNode, boolean required) {
        List<SqlNode> contents = getFieldValue(sqlNode, "contents");
        return contents.stream()
                .map(node -> parseSqlNode(node, required))
                .flatMap(Collection::stream)
                .collect(toList());
    }

    private static List<InputParam> parseTextSqlNodeParam(TextSqlNode sqlNode, boolean required) {
        TextSqlNodeTokenHandler handler1 = new TextSqlNodeTokenHandler();
        GenericTokenParser parser1 = new GenericTokenParser("${", "}", handler1);
        parser1.parse(getFieldValue(sqlNode, "text"));

        TextSqlNodeTokenHandler handler2 = new TextSqlNodeTokenHandler();
        GenericTokenParser parser2 = new GenericTokenParser("#{", "}", handler2);
        parser2.parse(getFieldValue(sqlNode, "text"));
        List<InputParam> all = new ArrayList<>();
        all.addAll(handler1.getParamSet().stream()
                .map(param -> {
                    ParameterExpression parameterExpression = new ParameterExpression(param);
                    return new InputParam(
                            parameterExpression.get(property.name()),
                            REPLACE,
                            null,
                            required,
                            parameterExpression.get(jdbcType.name())
                    );
                })
                .collect(toList()));
        all.addAll(handler2.getParamSet().stream()
                .map(param -> {
                    ParameterExpression parameterExpression = new ParameterExpression(param);
                    return new InputParam(
                            parameterExpression.get(property.name()),
                            PRE_COMPILE,
                            null,
                            required,
                            parameterExpression.get(jdbcType.name())
                    );
                })
                .collect(toList()));
        return all;
    }

    private static List<InputParam> parseRawParam(RawSqlSource sqlSource) {
        StaticSqlSource SqlSource = getFieldValue(sqlSource, "sqlSource");
        List<ParameterMapping> parameterMappings = getFieldValue(SqlSource, "parameterMappings");
        return parameterMappings.stream()
                .map(parameterMapping ->
                        new InputParam(parameterMapping.getProperty(), PRE_COMPILE, null, true,
                                Optional.ofNullable(parameterMapping.getJdbcType()).map(Enum::name).orElse("")))
                .collect(toList());
    }

    private static <T> T getFieldValue(Object o, String fieldName) {
        return getFieldValue(o.getClass(), o, fieldName);
    }

    private static <T> T getFieldValue(Class clazz, Object o, String fieldName) {
        try {
            Field field = clazz.getDeclaredField(fieldName);
            field.setAccessible(true);
            return (T) field.get(o);
        } catch (Exception e) {
            if (clazz.getSuperclass() != null) {
                return getFieldValue(clazz.getSuperclass(), o, fieldName);
            } else {
                return null;
            }
        }
    }

    public static SqlSource xmlVerify(String xml) {
        xml = String.format(verifyTemplate, xml);
        InputStream inputStream = new ByteArrayInputStream(xml.getBytes());
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        Map<String, XNode> sqlFragments = configuration.getSqlFragments();
        XMLMapperBuilder xmlMapperBuilder =
                new XMLMapperBuilder(inputStream, configuration, "Verify", sqlFragments);

        xmlMapperBuilder.parse();
        MappedStatement mappedStatement = configuration.getMappedStatement("verify", false);
        return mappedStatement.getSqlSource();
    }

    public static List<InputParam> parseInputParam(String xml) {
        SqlSource sqlSource = xmlVerify(xml);
        return parseWhereParam(sqlSource);
    }

    public SqlData parseSQLGetField(String sql, Map<String, String> map) {
        //入参
        Map<String, Object> params = new HashMap<>(16);
        //查询参数
        List<InputParam> paramList = new ArrayList<>();
        /**
         * 解析SQL语句，获取参数信息
         */
        List<InputParam> inputParams = parseInputParam(sql);
        Set<String> ifOrWhen = parse(sql);
        Set<String> bindTag = handBindTag(sql);
        for (InputParam inputParam : inputParams) {
            //必填入参
            String inputParamName = inputParam.getName();
            if (inputParam.isRequired() || StringUtils.isNotEmpty(map.get(inputParamName))) {
                params.put(inputParamName, map.get(inputParamName));
            }
            ifOrWhen.remove(inputParamName);
            //所有入参
            if (!bindTag.contains(inputParamName)) {
                paramList.add(inputParam);
            }

        }
        ifOrWhen.forEach(param -> {
            paramList.add(new InputParam(param, null, null, true));
        });
        SqlParser sqlParser = new SqlParser();
        SqlData sqlData = sqlParser.parse(sql, params);

        sqlData.setParamList(paramList);
        return sqlData;
    }

    private String processSqlByFreemarker(String sql, Map root) {
        String str = "";
        Configuration cfg = new Configuration();
        StringTemplateLoader stringLoader = new StringTemplateLoader();
        stringLoader.putTemplate("myTemplate", sql);
        cfg.setTemplateLoader(stringLoader);
        try {
            Template template = cfg.getTemplate("myTemplate", "utf-8");
            StringWriter writer = new StringWriter();
            template.process(root, writer);
            str = writer.toString();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (TemplateException e) {
            e.printStackTrace();
        }
        return str;
    }

    public Set<String> parse(String input) {
        String regex = "<if\\s+test\\s*=\\s*\"[^\"]*\">";
        Set<String> set = handIfWhenTag(input, regex);
        String regex2 = "<when\\s+test\\s*=\\s*\"[^\"]*\">";
        Set<String> set2 = handIfWhenTag(input, regex2);
        set.addAll(set2);
        return set;
    }

    private static Set<String> handIfWhenTag(String input, String regex) {
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(input);
        Set<String> set = new HashSet<>();
        while (matcher.find()) {
            String group = matcher.group();
            int first = group.indexOf("\"") + 1;
            int last = group.lastIndexOf("\"");
            String substring = group.substring(first, last);
            String tmp = "";
            if (substring.contains("==")) {
                tmp = substring.substring(0, substring.indexOf("=="));
            } else if (substring.contains("!=")) {
                tmp = substring.substring(0, substring.indexOf("!="));
            } else if (substring.contains(".size()")) {
                tmp = substring.substring(0, substring.indexOf(".size()"));
            }
            set.add(tmp.trim());
        }
        return set;
    }

    private static Set<String> handBindTag(String sql) {
        String regex = "<bind\\s+name=\"([^\"]+)\"\\s+value=\"([^\"]+)\"/>";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(sql);
        Set<String> set = new HashSet<>();
        while (matcher.find()) {
            String group = matcher.group();
            String substring = group.substring(group.indexOf('=') + 1, group.lastIndexOf("value")).trim().replace("\"", "");
            set.add(substring);
        }
        return set;
    }

    private static final String verifyTemplate = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n"
            + "<!DOCTYPE mapper\n"
            + "        PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\"\n"
            + "        \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">\n"
            + "<mapper namespace=\"Verify\">\n"
            + "    <select id=\"verify\">\n"
            + "    %s\n"
            + "    </select>\n"
            + "</mapper>";

    static class TextSqlNodeTokenHandler implements TokenHandler {

        private final Set<String> paramSet = new HashSet<String>();

        public Set<String> getParamSet() {
            return paramSet;
        }

        @Override
        public String handleToken(String content) {
            paramSet.add(content);
            return content;
        }
    }
}
